package com.javaweb.util.core;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

/**
 * JDBC工具类
 * <p>
 * 原始JDBC使用：
 * Connection connection = null;
 * PreparedStatement preparedStatement = null;
 * ResultSet resultSet = null;
 * Class.forName("com.mysql.cj.jdbc.Driver");
 * String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&autoReconnect=true&allowMultiQueries=true&serverTimezone=GMT%2B8";
 * String username = "root";
 * String password = "root";
 * connection = DriverManager.getConnection(url,username,password);
 * String sql = "select * from module where module_name like ?";
 * preparedStatement = connection.prepareStatement(sql);
 * preparedStatement.setString(1,"%管理%");
 * resultSet = preparedStatement.executeQuery();
 * while(resultSet.next()) {
 *     System.out.println(resultSet.getObject(1)+"，"+resultSet.getObject(2)+"，"+resultSet.getObject(3));
 * }
 * resultSet.close();
 * preparedStatement.close();
 * connection.close();
 * 封装后使用：
 * String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&autoReconnect=true&allowMultiQueries=true&serverTimezone=GMT%2B8";
 * String username = "root";
 * String password = "root";
 * String sql = "select * from sys_module where module_name like ?";
 * List<Map<String,Object>> list = JdbcUtil.getResultSet(url, username, password, sql,(preparedStatement)->{
 * //List<String> list = JdbcUtil.getColumnNameList(url, username, password, sql,(preparedStatement)->{
 *     try {
 *         preparedStatement.setString(1,"%管理%");
 *     } catch (Exception e) {
 *         //do nothing
 *     }
 * });   
 * System.out.println(list);
 * <p>
 * @author tank2140896
 */
public class JdbcUtil {
	
    /**
     * 加载驱动
     */
	static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            //do nothing
        }
    }
	
	/**
	 * 获得结果集
	 * @param url 连接URL
	 * @param username 用户名
	 * @param password 密码
	 * @param sql sql语句
	 * @param preparedStatementLambda PreparedStatement
	 * @return List<Map<String,Object>> 结果集
	 */
	public static List<Map<String,Object>> getResultSet(String url,String username,String password,String sql,PreparedStatementLambda preparedStatementLambda){
		List<Map<String,Object>> list = new ArrayList<>();
		try {
			Connection connection = DriverManager.getConnection(url,username,password);
			PreparedStatement preparedStatement = connection.prepareStatement(sql);
			preparedStatementLambda.excute(preparedStatement);
			ResultSet resultSet = preparedStatement.executeQuery();
			while(resultSet.next()){
				Map<String,Object> map = new HashMap<>(); 
				ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
				int columnCount = resultSetMetaData.getColumnCount();
				for(int i=1;i<=columnCount;i++){
					map.put(resultSetMetaData.getColumnName(i),resultSet.getObject(i));
				}
				list.add(map);
			}
			if(resultSet!=null){
				resultSet.close();
	    	}
	    	if(preparedStatement!=null){
    			preparedStatement.close();
	    	}
	    	if(connection!=null){
				connection.close();
	    	}
		} catch (Exception e) {
			//do nothing
		}
		return list;
	}
	
	/**
	 * 获得返回字段
	 * @param url 连接URL
	 * @param username 用户名
	 * @param password 密码
	 * @param sql sql语句
	 * @param preparedStatementLambda preparedStatementLambda
	 * @return List<String> 返回字段列表
	 */
	public static List<String> getColumnNameList(String url,String username,String password,String sql,PreparedStatementLambda preparedStatementLambda){
		List<String> columnNameList = new ArrayList<>();
		try{
			Connection connection = DriverManager.getConnection(url,username,password);
			PreparedStatement preparedStatement = connection.prepareStatement(sql);
			preparedStatementLambda.excute(preparedStatement);
			ResultSet resultSet = preparedStatement.executeQuery();
			ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
			int columnCount = resultSetMetaData.getColumnCount();
			for(int i=1;i<=columnCount;i++){
				columnNameList.add(resultSetMetaData.getColumnName(i));
			}
		}catch(Exception e){
			//do nothing
		}
		return columnNameList;
	}
    
	/**
	 * PreparedStatementLambda
	 * @author tank2140896
	 */
    public interface PreparedStatementLambda {
    	
    	/**
    	 * 执行操作
    	 * @param preparedStatement PreparedStatement
    	 */
    	public void excute(PreparedStatement preparedStatement);

    }
    
    public static void main(String[] args) throws Exception {
    	String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&autoReconnect=true&allowMultiQueries=true&serverTimezone=GMT%2B8";
    	String username = "root";
    	String password = "root";
    	Connection connection = DriverManager.getConnection(url,username,password);
    	connection.setAutoCommit(false);
    	String sql = "insert into sys_operation_log(id,user_id,url,base_url,request_header,request_method,request_parameter,request_ip_address,request_time,login_way,client_type,remark) values(?,?,?,?,?,?,?,?,?,?,?,?)";
    	PreparedStatement preparedStatement = connection.prepareStatement(sql);
    	long start = System.currentTimeMillis();
    	String uuid = UUID.randomUUID().toString();
    	java.util.Date date = new java.util.Date();
    	java.sql.Date sqlDate = new java.sql.Date(date.getTime());
    	for(int i=1;i<=100000;i++) {
    		int temp_i = i;
    		preparedStatement.setInt(1,temp_i);
    		preparedStatement.setInt(2,temp_i);
    		preparedStatement.setString(3,uuid);
    		preparedStatement.setString(4,uuid);
    		preparedStatement.setString(5,uuid);
    		preparedStatement.setString(6,uuid);
    		preparedStatement.setString(7,uuid);
    		preparedStatement.setString(8,uuid);
    		preparedStatement.setDate(9,sqlDate);
    		preparedStatement.setInt(10,1);
    		preparedStatement.setInt(11,2);
			preparedStatement.setString(12,uuid);
			preparedStatement.addBatch();
    		if(i!=0&&i%1000==0) {
    			preparedStatement.executeBatch();
    			preparedStatement.clearBatch();
    		}
    	}
    	preparedStatement.executeBatch();
		preparedStatement.clearBatch();
    	connection.commit();
    	preparedStatement.close();
    	connection.close();
    	long end = System.currentTimeMillis();
    	System.out.println((end-start)/1000);
    }
    
}
